<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*"%>
				<%@ page import="javax.sql.rowset.CachedRowSet,com.sun.rowset.CachedRowSetImpl;" %>
				<%
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");
	
						String action = request.getParameter("action");
						// Check if an insertion is requested
						if (action != null && action.equals("insert")) {
							// Begin transaction
							conn.setAutoCommit(false);
							
							String[] faculty = request.getParameterValues("faculty");
							if (faculty != null) {
								for (String prof : faculty) {
									PreparedStatement pstmt = conn
											.prepareStatement("INSERT INTO committee VALUES (?, ?)");
									pstmt.setString(1, request.getParameter("student_id"));
									pstmt.setString(2, prof);
									pstmt.executeUpdate();
								}
							}
							
							// Commit transaction
							conn.commit();
							conn.setAutoCommit(true);
						}
						// Check if an update is requested
						if (action != null && action.equals("update")) {
							// Begin transaction
	                        conn.setAutoCommit(false);
							
	                     	// Avoid insert duplicates entries by deleting all exising entries first.
	                     	PreparedStatement pstmt = conn.prepareStatement("DELETE FROM committee WHERE student_id = ?");
	                     	pstmt.setString(1, request.getParameter("student_id"));
	                     	pstmt.executeUpdate();
	                     	
	                     	String[] faculty = request.getParameterValues("faculty");
							if (faculty != null) {
								for (String prof : faculty) {
									pstmt = conn.prepareStatement("INSERT INTO committee VALUES (?, ?)");
									pstmt.setString(1, request.getParameter("student_id"));
									pstmt.setString(2, prof);
									pstmt.executeUpdate();
								}
							}
	                     	
							// Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
						}
						
						// Check if a delete is requested
	                    if (action != null && action.equals("delete")) {
	                    	// Begin transaction
	                        conn.setAutoCommit(false);
	                        
	                        // Create the prepared statement and use it to
	                        // DELETE the student FROM the committee table.
	                        PreparedStatement pstmt = conn.prepareStatement(
	                            "DELETE FROM committee WHERE student_id = ?");
	                        pstmt.setString(1, request.getParameter("student_id"));
	                        pstmt.executeUpdate();

	                        // Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
	                    }
						
						// Create the statement
						Statement statement = conn.createStatement();
						
						// Use the created statement to SELECT
						// the grad student attributes.
						SortedMap<String, String> students = new TreeMap<String, String>();
						ResultSet rsGrad = statement.executeQuery("SELECT s.student_id, first_name, " + 
								"last_name, 'MS' AS level FROM student s, ms_student m WHERE " + 
								"s.student_id = m.student_id UNION SELECT s.student_id, first_name, " + 
								"last_name, 'PhD' AS level FROM student s, phd_student m WHERE " + 
								"s.student_id = m.student_id");
						while (rsGrad.next()) {
							String info = rsGrad.getString("first_name") + " " + 
									rsGrad.getString("last_name") + ", " + rsGrad.getString("level");
							students.put(rsGrad.getString("student_id"), info);
						}
						
						// SELECT faculty
						ResultSet rsFaculty = statement.executeQuery("SELECT * FROM faculty");
						SortedMap<String, String> faculty = new TreeMap<String, String>();
						while (rsFaculty.next()) {
							faculty.put(rsFaculty.getString("name"), rsFaculty.getString("department"));
						}
						
						// SELECT student who has a committee
						ResultSet rsStudents = statement.executeQuery("SELECT DISTINCT student_id FROM committee");
						CachedRowSet rs = new CachedRowSetImpl();
				        rs.populate(rsStudents);
				%>
				<table border="1">
					<tr>
						<th>Student</th>
						<th>Committee</th>
					</tr>
					<tr>
						<form action="committee.jsp" method="POST">
							<input type="hidden" value="insert" name="action">
							<th>
								<select name="student_id" style="width:200px">
								<%
									for (Map.Entry<String, String> student : students.entrySet()) {
								%>
						 			<option value="<%= student.getKey() %>">
						 				<%= student.getValue() + " (" + student.getKey() + ")" %>
						 			</option>
								<%
									}
								%>
								</select>
							</th>
							<th>
								<select name="faculty" size="3" multiple="true" style="width:400px">
								<%
									for (Map.Entry<String, String> prof : faculty.entrySet()) {
								%>
						 			<option value="<%= prof.getKey() %>">
						 				<%= prof.getKey() + " (" + prof.getValue() + ")" %>
						 			</option>
								<%
									}
								%>
								</select>
							</th>
							<th><input type="submit" value="Insert"></th>
						</form>
					</tr>
					<%
						// Iterate over the ResultSet
						while (rs.next()) {  
					%>
					<tr>
                        <form action="committee.jsp" method="POST">
                        	<input type="hidden" value="update" name="action">
                        	<td>
                        		<input type="hidden" value="<%= rs.getString("student_id") %>" name="student_id">
                        		<%
                        			String student = students.get(rs.getString("student_id"));
                        			out.println(student + " (" + rs.getString("student_id") + ")");
                        		%>
                        	</td>
                        	<td>
                        		<select name="faculty" size="3" multiple="true" style="width:400px">
                        		<%
                        			PreparedStatement pstmt = conn.prepareStatement("SELECT faculty_name " +
                        					"FROM committee WHERE student_id = ?");
                        			pstmt.setString(1, rs.getString("student_id"));
                        			ResultSet rsComm = pstmt.executeQuery();
                        			Set<String> committee = new HashSet<String>();
                        			while (rsComm.next()) {
                        				committee.add(rsComm.getString("faculty_name"));
                        			}
                        			for (Map.Entry<String, String> prof : faculty.entrySet()) {
                        		%>
                        			<option value="<%= prof.getKey() %>" 
                        					<% if (committee.contains(prof.getKey())) out.print("selected='true'"); %>>
						 				<%= prof.getKey() + " (" + prof.getValue() + ")" %>
						 			</option>
						 		<%
                        			}
                        		%>
                        		</select>
                        	</td>
                        	<td>
                                <input type="submit" value="Update">
                            </td>
                        </form>
                        <form action="committee.jsp" method="get">
                            <input type="hidden" value="delete" name="action">
                            <input type="hidden" 
                                value="<%= rs.getString("student_id") %>" name="student_id">
                            <td>
                                <input type="submit" value="Delete">
                            </td>
                        </form>
                    </tr>
                <%
						}
                %>
				<%
					} catch (SQLException e) {
						System.out.println(e.getMessage());
						conn.rollback();
					} catch (Exception e) {
						System.out.println(e.getMessage());
						conn.rollback();
					}
				%>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>